﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Security.AccessControl;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Windows.Markup;

namespace NW2Example
{
    public partial class Form1 : Form
    {
        private BindingSource bindingSource1 = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        private const string SELECT = "Select * from Customers";
        public Form1()
        {
            InitializeComponent();
            this.Load += new System.EventHandler(Form1_Load);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = bindingSource1;
            LoadDataGridView();
        }

        public void GetData(string selectCommand)
        {
            try
            {
                string connectionString =
                    ConfigurationManager.ConnectionStrings["northwindConnection"].ConnectionString;
                dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
                DataTable table = new DataTable();
                dataAdapter.Fill(table);
                bindingSource1.DataSource = table;
            }
            catch (Exception)
            {

                MessageBox.Show("error");
            }
        }

        public void LoadDataGridView()
        {
            string cmd = "SELECT * FROM Categories";
            GetData(SELECT);

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                tbEditCustomerID.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
                tbEditCompanyName.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
                tbEditContactName.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (var con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["northwindConnection"].ConnectionString;
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string ID = tbEditCustomerID.Text.Trim(), CompanyName = tbEditCompanyName.Text.Trim(), ContactName = tbEditContactName.Text.Trim();

                string alter = string.Format("UPDATE Customers SET CustomerID = '{0}', CompanyName = '{1}', ContactName = '{2}' WHERE CustomerID = '{0}'",
                    ID, CompanyName, ContactName);
                cmd.CommandText = alter;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            LoadDataGridView();
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["northwindConnection"].ConnectionString;
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName) VALUES('" +
                                  tbAddCustomerID.Text.Trim() + "', '" + tbAddCompanyName.Text.Trim() +
                "', '" + tbAddContactName.Text.Trim() + "')";
                con.Open();
                int count = cmd.ExecuteNonQuery();
                lbAddResult.Text = string.Format("Number of rows updated {0}", count);
            }
            LoadDataGridView();
        }
    }
}
